<?php

require_once('../lib/SqliteTriggerGenerator.php');
require_once('simpletest/autorun.php');

$dsn = 'sqlite::memory:';
#$dsn = 'sqlite:foo.db';

abstract class BaseFkTests extends UnitTestCase {
    function setUp() {
        global $dsn;
        $this->conn = new PDO($dsn);
        $this->conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

        $count = $this->conn->exec($this->persons_sql);
        $this->conn->exec($this->addresses_sql);
        
        $gen = new SqliteTriggerGenerator( false, false );
        $tsql = $gen->create( $this->addresses_sql );
        
        foreach( $tsql as $statement) {
            $this->conn->exec($statement);
        }
        
        $this->conn->exec($this->phones_sql);
        
        $gen = new SqliteTriggerGenerator( false, false );
        $tsql = $gen->create( $this->phones_sql );
        
        foreach( $tsql as $statement) {
            $this->conn->exec($statement);
        }
        
        $sql = "INSERT INTO persons (id, name) VALUES (1, 'test person')";
        $this->conn->exec($sql);
    }

    function tearDown(){
        $this->conn = null;
    }
    
    private function rowcount($table) {
        $sql = "SELECT count(*) FROM $table";
        
        $res = $this->conn->query($sql);
        return $res->fetchColumn();
    }
    
    private function updatesetup() {
        $sql = "INSERT INTO persons (id, name) VALUES (2, 'test person2')";
        $count = $this->conn->exec($sql);
        $sql = "INSERT INTO persons (id, name) VALUES (3, 'test person3')";
        $count = $this->conn->exec($sql);
        
        $sql = "INSERT INTO addresses (id, persons_id, street) VALUES (1, 1, '123 main st.')";
        $count = $this->conn->exec($sql);
        
        $sql = "INSERT INTO phones (id, persons_id, number) VALUES (1, 2, '12343456')";
        $count = $this->conn->exec($sql);
    }

    function testOkInsert() {
        $sql = "INSERT INTO persons (name) VALUES ('test person2')";
        $count = $this->conn->exec($sql);
        $this->assertEqual(1, $count);
        
        $sql = "INSERT INTO addresses (persons_id, street) VALUES (1, '123 main st.')";
        $count = $this->conn->exec($sql);
        $this->assertEqual(1, $count);
    }

    function testBadInsert() {
        try {
            $sql = "INSERT INTO addresses (persons_id, street) VALUES (10, '456 main st.')";
            $count = $this->conn->exec($sql);
            $this->fail('expected an exception to be thrown since person_id doesn\'t exist');
        } catch (Exception $e) {
            if( !stristr($e->getMessage(), 'insert on table "addresses" violates foreign key constraint') ){
                $this->fail('exception did not have the expected message');
            }
        }
    }

    function testOkNullInsert() {        
        $sql = "INSERT INTO phones (persons_id, number) VALUES (NULL, '5555555555')";
        $count = $this->conn->exec($sql);
        $this->assertEqual(1, $count);
    }

    function testOkUpdate() {
        $this->updatesetup();
        $sql = "UPDATE addresses SET persons_id = 2 where id = 1";
        $count = $this->conn->exec($sql);
        $this->assertEqual(1, $count);
    }

    function testBadUpdate() {
        $this->updatesetup();
        try {
            $sql = "UPDATE addresses SET persons_id = 10 where id = 1";
            $count = $this->conn->exec($sql);
            $this->fail('expected an exception to be thrown since person_id doesn\'t exist');
        } catch (Exception $e) {
            if( !stristr($e->getMessage(), 'update on table "addresses" violates foreign key constraint') ){
                $this->fail('exception did not have the expected message');
            }
        }
    }

    function testBadNullUpdate() {
        $this->updatesetup();
        try {
            $sql = "UPDATE addresses SET persons_id = NULL where id = 1";
            $count = $this->conn->exec($sql);
            $this->fail('expected an exception to be thrown since person_id should not be NULL');
        } catch (Exception $e) {
            if( !stristr($e->getMessage(), 'update on table "addresses" violates foreign key constraint') ){
                $this->fail('exception did not have the expected message');
            }
        }
    }

    function testGoodNullUpdate() {
        $this->updatesetup();
        $sql = "UPDATE phones SET persons_id = NULL where id = 1";
        $count = $this->conn->exec($sql);
        $this->assertEqual(1, $count);
    }

    function testGoodDelete(){
        $this->updatesetup();
        $this->assertEqual(3, $this->rowcount('persons'));
        $sql = "DELETE FROM persons WHERE id = 3";
        $count = $this->conn->exec($sql);
        $this->assertEqual(1, $count);
        $this->assertEqual(2, $this->rowcount('persons'));
    }
    
    function testBadDelete(){
       $this->updatesetup();
        try {
            $sql = "DELETE FROM persons WHERE id = 1";
            $count = $this->conn->exec($sql);
            $this->fail('expected an exception to be thrown since the person has dependencies');
        } catch (Exception $e) {
            if( !stristr($e->getMessage(), 'delete on table "persons" violates foreign key constraint') ){
                $this->fail('exception did not have the expected message');
            }
        }
        
    }
    
    function testCascadeDelete(){
        $this->updatesetup();
        $this->assertEqual(3, $this->rowcount('persons'));
        $this->assertEqual(1, $this->rowcount('phones'));
        $sql = "DELETE FROM persons WHERE id = 2";
        $count = $this->conn->exec($sql);
        $this->assertEqual(1, $count);
        $this->assertEqual(2, $this->rowcount('persons'));
        $this->assertEqual(0, $this->rowcount('phones'));        
    }
}

class sqlvariant1 extends BaseFkTests {
    public $persons_sql = '
            CREATE TABLE persons (
                id INTEGER NOT NULL PRIMARY KEY,
                name VARCHAR(16) NOT NULL
            );';
    
    public $addresses_sql = '
            CREATE TABLE addresses (
                id INTEGER NOT NULL PRIMARY KEY,
                persons_id INTEGER NOT NULL
                    CONSTRAINT fk_addresses_persons_id REFERENCES persons(id),
                street VARCHAR(40) NOT NULL
            );';
    
    public $phones_sql = '
            CREATE TABLE phones (
                id INTEGER NOT NULL PRIMARY KEY,
                persons_id INTEGER
                    CONSTRAINT fk_phones_persons_id REFERENCES persons(id) ON DELETE CASCADE,
                number VARCHAR(10) NOT NULL
            );';
}

class sqlvariant2 extends BaseFkTests {
    public $persons_sql = '
            CREATE TABLE persons (
                id INTEGER NOT NULL PRIMARY KEY,
                name VARCHAR(16) NOT NULL
            );';
    
    public $addresses_sql = '
            CREATE TABLE "addresses" (
                "id" INTEGER NOT NULL PRIMARY KEY,
                "persons_id" INTEGER NOT NULL
                    CONSTRAINT "fk_addresses_persons_id" REFERENCES persons("id"),
                "street" VARCHAR(40) NOT NULL
            );';
    
    public $phones_sql = '
            CREATE TABLE [phones] (
                [id] INTEGER NOT NULL PRIMARY KEY,
                [persons_id] INTEGER
                    CONSTRAINT [fk_phones_persons_id] REFERENCES persons([id]) ON DELETE CASCADE,
                [number] VARCHAR(10) NOT NULL
            );';
}

?>
